import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_就业单位分布_前n项名(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    top_n: int = 3
) -> Dict[str, Any]:
    """
    就业单位分布-前n项名 - 指标计算函数
    
    ## 指标说明
    该指标用于统计毕业生就业单位性质分布情况，计算不同单位性质的就业人数占比，
    并返回占比最高的前n项单位性质类型。数据来源于学生客观表，要求毕业去向不为
    待就业和暂不就业，且单位性质字段不为空。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        top_n (int): 返回前n项单位性质，默认为3
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": {
            "summary": {
                "top_n": 3,
                "total_count": 1387,
                "top_companies_text": "其他企业、国有企业和高等教育单位",
                "description": "前3项单位性质占就业总数的 89.66%"
            },
            "distribution": [
                {
                    "company_nature": "其他企业",
                    "count": 1195,
                    "ratio": 0.7725,
                    "percentage": "77.25%"
                },
                {
                    "company_nature": "国有企业",
                    "count": 126,
                    "ratio": 0.0814,
                    "percentage": "8.14%"
                },
                {
                    "company_nature": "高等教育单位",
                    "count": 66,
                    "ratio": 0.0427,
                    "percentage": "4.27%"
                }
            ],
            "legacy_format": [
                {
                    "calcVals": [
                        {
                            "val": [
                                {
                                    "key": "default",
                                    "val": "其他企业、国有企业和高等教育单位"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 就业单位分布-前n项名, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询单位性质分布数据
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        sql = f"""
        SELECT 
            company_nature, 
            count(*) as count, 
            count(*)/(
                SELECT count(*) 
                FROM {student_table} 
                WHERE shard_tb_key = %s 
                AND grad_dest_merge != '待就业' 
                AND grad_dest_merge != '暂不就业' 
                AND company_nature != ''
            ) AS ratio_val
        FROM {student_table}
        WHERE
            shard_tb_key = %s
            AND grad_dest_merge != '待就业'
            AND grad_dest_merge != '暂不就业'
            AND company_nature != ''
        GROUP BY company_nature
        ORDER BY ratio_val DESC
        LIMIT %s
        """
        results = db.fetchall(sql, (shard_tb_key, shard_tb_key, top_n))
        if not results:
            raise ValueError("未找到有效的单位性质分布数据")
        
        logger.info(f"查询到单位性质分布数据: {results}")

        # 4. 处理查询结果
        top_companies = [row['company_nature'] for row in results]
        result_str = "、".join(top_companies)
        
        # 计算总数和前n项占比
        total_count = sum(row['count'] for row in results)
        top_n_count = sum(row['count'] for row in results)
        top_n_ratio = sum(row['ratio_val'] for row in results)
        
        # 构建分布明细
        distribution = []
        for row in results:
            distribution.append({
                "company_nature": row['company_nature'],
                "count": row['count'],
                "ratio": round(float(row['ratio_val']), 4),
                "percentage": f"{float(row['ratio_val']) * 100:.2f}%"
            })
        
        logger.info(f"前{top_n}项单位性质: {result_str}")
        logger.info(f"总计 {total_count} 人，前{top_n}项占比: {top_n_ratio:.4f}")
        logger.info(f"各单位性质分布: {[(item['company_nature'], item['count'], item['percentage']) for item in distribution]}")

        # 5. 构建返回数据
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "summary": {
                    "top_n": top_n,
                    "total_count": total_count,
                    "top_companies_text": result_str,
                    "description": f"前{top_n}项单位性质占就业总数的 {top_n_ratio * 100:.2f}%"
                },
                "distribution": distribution,
                "legacy_format": [
                    {
                        "calcVals": [
                            {
                                "val": [
                                    {
                                        "key": "default",
                                        "val": result_str
                                    }
                                ]
                            }
                        ]
                    }
                ]
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '就业单位分布-前n项名' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 就业单位分布-前n项名",
            "code": 500,
            "error": str(e)
        }